package com.tpc.control.util;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Timestamp;
import java.text.NumberFormat;
import java.util.List;
import java.util.Locale;

import javax.faces.context.FacesContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
import javax.swing.JFileChooser;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.tpc.control.jpa.EntityManagerHelper;
import com.tpc.control.jpa.ReserveHeader;
import com.tpc.control.jpa.ReserveHeaderDAO;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.BoldStyle;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;



public class WriteExcel extends HttpServlet {


	private WritableCellFormat times;
	private WritableCellFormat timesHeader;
	private WritableCellFormat timesTitle;
	private WritableCellFormat timesBold;

	private static int excelIndex = 0;
	
	

	private static File file;
	private static File xfile;
	
	public void write() {
		javax.faces.context.FacesContext conText = javax.faces.context.FacesContext
		.getCurrentInstance();
		
		javax.servlet.http.HttpServletResponse response = (javax.servlet.http.HttpServletResponse) conText
		.getExternalContext().getResponse();
		
		try {
			   response.setContentType("application/vnd.ms-excel");
			   response.setHeader
			     ("Content-Disposition", "attachment; filename=sampleName.xls");
			   WritableWorkbook w = 
			     Workbook.createWorkbook(response.getOutputStream());
			   WritableSheet s = w.createSheet("Demo", 0);
			   s.addCell(new Label(0, 0, "Hello World"));
			   w.write();
			   w.close();
		} 
		catch (Exception e){
			   e.printStackTrace();
		 } 
		conText.getApplication().getStateManager().saveSerializedView(conText);
		conText.responseComplete();
	}
	
	public void write(List<ReportEntity> dataList,List reserveNoLists) throws IOException, WriteException {
		

		javax.faces.context.FacesContext conText = javax.faces.context.FacesContext
		.getCurrentInstance();
		
javax.servlet.http.HttpServletResponse response = (javax.servlet.http.HttpServletResponse) conText
		.getExternalContext().getResponse();
	
		

		   
		   OutputStream out = null;
		   
		   try {
			   
		

		    WorkbookSettings wbSettings = new WorkbookSettings();

			wbSettings.setLocale(new Locale("en", "EN"));

			response.setContentType("application/vnd.ms-excel");

			response.setHeader
			      ("Content-Disposition", "attachment; filename=reserve_report.xls");
			WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream());

			WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableFont times14pt = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableFont timesBoldpt = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			
			times = new WritableCellFormat(times10pt);
			timesHeader = new WritableCellFormat(times12pt);
			timesTitle = new WritableCellFormat(times14pt);
			timesBold = new WritableCellFormat(timesBoldpt);
			
			
			for(int i=0;i<reserveNoLists.size();i++)
			{
				workbook.createSheet("Sheet_" + i, i);
				
				WritableSheet excelSheet = workbook.getSheet(i);
				
				
				excelSheet.setColumnView(0, 5);
				excelSheet.setColumnView(1, 15);
				excelSheet.setColumnView(2, 15);
				excelSheet.setColumnView(3, 6);
				excelSheet.setColumnView(4, 10);
				excelSheet.setColumnView(5, 10);
				excelSheet.setColumnView(6, 10);
				excelSheet.setColumnView(7,10);

		
				excelSheet.mergeCells(4, 0, 5, 0);
				
				excelSheet.mergeCells(0, 2, 1, 2);
				excelSheet.mergeCells(0, 3, 1, 3);
				excelSheet.mergeCells(0, 4, 1, 4);
		
				excelSheet.mergeCells(2, 2,7, 2);
				excelSheet.mergeCells(2, 3,7, 3);
				excelSheet.mergeCells(2, 4,7, 4);
				
				CellView cv = new CellView();
				cv.setFormat(times);

				cv.setAutosize(true);

				// Write a few headers
				addTitle(excelSheet, 4, 0, "ข้อมูลใบจอง");

				addHeader(excelSheet,0,2,"เลขที่ใบจอง");
				addHeader(excelSheet,0,3,"รหัสลูกค้า");
				addHeader(excelSheet,0,4,"ชื่อลูกค้า");
				
				
				addBold(excelSheet,0,7,"ลำดับ");
				addBold(excelSheet,1,7,"รหัส com");
				addBold(excelSheet,2,7,"รหัส design");
				addBold(excelSheet,3,7,"แผนก");
				addBold(excelSheet,4,7,"ปริมาณจอง");
				addBold(excelSheet,5,7,"หน่วย");
				addBold(excelSheet,6,7,"กำหนดส่ง");
				addBold(excelSheet,7,7,"สถานะ");

			
				ReserveHeaderDAO dao = new ReserveHeaderDAO();
				EntityManagerHelper.beginTransaction();
				ReserveHeader header = dao.findById((String)reserveNoLists.get(i));
				EntityManagerHelper.commit();
				EntityManagerHelper.closeEntityManager();
				
				addHeader(excelSheet,2,2,reserveNoLists.get(i).toString());
				addHeader(excelSheet,2,3,header.getCustomer());
				addHeader(excelSheet,2,4,header.getCustomerName());		
				
				Timestamp date = header.getCreateDate();
				String Date = String.valueOf(date.getDate());
				if(Date.length()<2)
				{
					Date = "0" + Date;
				}
				String Month = String.valueOf(date.getMonth() + 1);
				if(Month.length()<2)
				{
					Month = "0" + Month;
				}
				String Year = String.valueOf(date.getYear() + 1900);
				String createDate = Date + "/" + Month + "/" + Year;
				
				addBold(excelSheet,1,51,"วันที่สร้างใบจองͧ");	
				addBold(excelSheet,2,51,createDate);	
				
				addBold(excelSheet,5,51,"ชื่อผู้จอง");
				addBold(excelSheet,6,51,header.getCreateBy());
				
				
				int k=0;
				for(int j=0;j<dataList.size();j++)
				{
					ReportEntity report = dataList.get(j);
					if(report.getReserveNo().toUpperCase().equals(reserveNoLists.get(i).toString().toUpperCase()))
					{
						addLabel(excelSheet,0,9+k,report.getLine());
						addLabel(excelSheet,1,9+k,report.getMaterialNo());
						addLabel(excelSheet,2,9+k,report.getMaterialDesign());
						addLabel(excelSheet,3,9+k,report.getDivision());
						addLabel(excelSheet,4,9+k,report.getQty());
						addLabel(excelSheet,5,9+k,report.getUom());
						addLabel(excelSheet,6,9+k,report.getDeliveryDate());
						addLabel(excelSheet,7,9+k,report.getStatus());
						k++;
					}
				}
				excelSheet.addRowPageBreak(52);
			}
					    
			    //javax.servlet.ServletOutputStream out = response.getOutputStream();
			
			
		    workbook.write();
		    workbook.close();
		    
		   } 
		   catch (Exception e){
		    try {
				throw new ServletException("Exception in Excel Sample Servlet", e);
			} catch (ServletException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		   } 
		   finally{
		    if (out != null)
		     out.close();
		   }
		   
		   
		   conText.getApplication().getStateManager().saveSerializedView(conText);
			conText.responseComplete();
	}

	private void createLabel(WritableSheet sheet)
			throws WriteException {
		
	}

	private void createContent(WritableSheet sheet,List<ReportEntity> dataList) throws WriteException,
			RowsExceededException {
		
		
		for(int i=0;i<dataList.size();i++)
		{
			/*
			List<String> list = dataList.get(i);
			for(int j=0;j<list.size();j++)
			{
				addLabel(sheet,j,9+i,list.get(j));
			}
			*/
			
			addLabel(sheet,0,9+i,dataList.get(i).getLine());
			addLabel(sheet,1,9+i,dataList.get(i).getMaterialNo());
			addLabel(sheet,2,9+i,dataList.get(i).getMaterialDesign());
			addLabel(sheet,3,9+i,dataList.get(i).getDivision());
			addLabel(sheet,4,9+i,dataList.get(i).getQty());
			addLabel(sheet,5,9+i,dataList.get(i).getUom());
			addLabel(sheet,6,9+i,dataList.get(i).getDeliveryDate());
			addLabel(sheet,7,9+i,dataList.get(i).getStatus());	
		}
		
	}

	private void addCaption(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, timesHeader);
		sheet.addCell(label);
	}
	
	private void addTitle(WritableSheet sheet, int column, int row, String s)
	throws RowsExceededException, WriteException {
Label label;
label = new Label(column, row, s, timesTitle);
sheet.addCell(label);
}

	private void addNumber(WritableSheet sheet, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer, times);
		sheet.addCell(number);
	}

	private void addLabel(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}
	private void addHeader(WritableSheet sheet, int column, int row, String s)
	throws WriteException, RowsExceededException {
Label label;
label = new Label(column, row, s, timesHeader);
sheet.addCell(label);

}
	
	private void addBold(WritableSheet sheet, int column, int row, String s)
	throws WriteException, RowsExceededException {
Label label;
label = new Label(column, row, s, timesBold);
sheet.addCell(label);
	}

	public static void generateReport(List<ReportEntity> dataList,List reserveNoLists) throws WriteException, IOException
	{
		WriteExcel we = new WriteExcel();
		
		try
		{	
			we.write(dataList,reserveNoLists);
		}
		catch(Exception ex)
		{
			System.out.println(ex);
		}
	}
	

}

